package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.x.erp.model.CarDel;
import com.x.framework.dao.BaseDao;

@Repository
public class CarDelDao extends BaseDao {
	private final static String selectCarDelSql = "SELECT " +
			"ecd.*, " +
			"bca.CAR_AGE_REMARK, " +
			"bsa.STOCK_AGE_REMARK, " +
			"bb.BRAND_NAME, " +
			"bs.SERIES_NAME, " +
			"bm.MODEL_NAME, " +
			"bm.MODEL_YEAR, " +
			"er.REGION_NAME, " +
			"bct.CAR_TYPE_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE " +
			"FROM " +
			"ERP_CAR_DEL ecd  " +
			"JOIN x_base.BASE_BRAND bb ON ecd.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ecd.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.BASE_MODEL bm ON ecd.MODEL_ID = bm.MODEL_ID " +
			"JOIN ERP_SHOP es ON ecd.SHOP_ID = es.SHOP_ID " +
			"JOIN BASE_CAR_TYPE bct ON ecd.CAR_TYPE_ID = bct.CAR_TYPE_ID " +
			"LEFT JOIN BASE_CAR_AGE bca ON ecd.CAR_LICENCE_DATE >= bca.CAR_AGE_FROM AND ecd.CAR_LICENCE_DATE < bca.CAR_AGE_TO " +
			"LEFT JOIN BASE_STOCK_AGE bsa ON ecd.CAR_CREATE_TIME >= bsa.STOCK_AGE_FROM AND ecd.CAR_CREATE_TIME < bsa.STOCK_AGE_TO " +
			"LEFT JOIN ERP_REGION er ON ecd.REGION_ID = er.REGION_ID " +
			"WHERE 1=1 ";
	/**
	 * 查询车辆信息
	 * @param carDel
	 * @return
	 * @throws Exception
	 */
	public CarDel selectCarDel(CarDel carDel) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarDelSql);
		List<Object> params = new ArrayList<Object>();
		searchCondition(sql, params, carDel);
		List<CarDel> results = this.queryForList(sql.toString(), CarDel.class, params);
		if(results != null && results.size() > 0) {
			return results.get(0);
		}
		return null;
	}

	private final static String selectCarDelListSql = "SELECT " +
			"ecd.*, " +
			"bb.BRAND_NAME, " +
			"bs.SERIES_NAME, " +
			"er.REGION_NAME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_MOBILE, " +
			"es.SHOP_ADDRESS, " +
			"es.SHOP_LEGAL_NAME, " +
			"es.SHOP_LEGAL_MOBILE, " +
			"es.SHOP_PARK_NUM, " +
			"es.SHOP_CAR_NUM, " +
			"es.SHOP_MANAGE_NAME, " +
			"es.SHOP_MANAGE_MOBILE " +
			"FROM " +
			"ERP_CAR_DEL ecd  " +
			"JOIN x_base.BASE_BRAND bb ON ecd.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ecd.SERIES_ID = bs.SERIES_ID " +
			"JOIN ERP_MARKET em ON ecd.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_SHOP es ON ecd.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN ERP_REGION er ON ecd.REGION_ID = er.REGION_ID " +
			"WHERE 1=1 ";

	/**
	 * @param carDel 封装了carDel的检索条件
	 * @return 满足条件的carDellist
	 * @throws Exception
	 */
	public List<CarDel> selectCarDelList(CarDel carDel) throws Exception{
		StringBuilder sql = new StringBuilder(selectCarDelListSql);
		List<Object> params = new ArrayList<Object>();
		searchCondition(sql, params, carDel);
		sql.append("ORDER BY ecd.CAR_CREATE_TIME DESC ");
		return this.queryForList(sql.toString(), CarDel.class, params, carDel);
	}
	
	/**
	 * 查询条件
	 * @param sql
	 * @param params
	 * @param carDel
	 */
	private void searchCondition(StringBuilder sql, List<Object> params, CarDel carDel) {
		if(carDel != null){
			if(this.isString(carDel.getMarketId())){
				sql.append("AND ecd.MARKET_ID = ? ");
				params.add(carDel.getMarketId());
			}
			if(this.isString(carDel.getCarId())){
				sql.append("AND ecd.CAR_ID = ? ");
				params.add(carDel.getCarId());
			}
			if(this.isString(carDel.getShopId())){
				sql.append("AND ecd.SHOP_ID = ? ");
				params.add(carDel.getShopId());
			}
			if(carDel.getBrandId() != null){
				sql.append("AND ecd.BRAND_ID = ? ");
				params.add(carDel.getBrandId());
			}
			if(carDel.getCarTypeId() != null){
				sql.append("AND ecd.CAR_TYPE_ID = ? ");
				params.add(carDel.getCarTypeId());
			}
			if(this.isString(carDel.getCarOutColor())){
				sql.append("AND ecd.CAR_OUT_COLOR = ? ");
				params.add(carDel.getCarOutColor());
			}
			if(this.isString(carDel.getCarEnviron())){
				sql.append("AND ecd.CAR_ENVIRON = ? ");
				params.add(carDel.getCarEnviron());
			}
			if(carDel.getCarLicenceDate() != null){
				sql.append("AND ecd.CAR_LICENCE_DATE = ? ");
				params.add(carDel.getCarLicenceDate());
			}
			if(this.isString(carDel.getCarVin())){
				sql.append("AND ecd.CAR_VIN = ? ");
				params.add(carDel.getCarVin());
			}
			if(this.isString(carDel.getCarVin6())){
				sql.append("AND ecd.CAR_VIN6 = ? ");
				params.add(carDel.getCarVin6());
			}
			if(carDel.getModelId() != null){
				sql.append("AND ecd.MODEL_ID = ? ");
				params.add(carDel.getModelId());
			}
			if(carDel.getSeriesId() != null){
				sql.append("AND ecd.SERIES_ID = ? ");
				params.add(carDel.getSeriesId());
			}
			if(this.isString(carDel.getUserId())){
				sql.append("AND ecd.USER_ID = ? ");
				params.add(carDel.getUserId());
			}
			if(carDel.getCarFloorPrice() != null){
				sql.append("AND ecd.CAR_FLOOR_PRICE = ? ");
				params.add(carDel.getCarFloorPrice());
			}
			if(carDel.getCarRetailPrice() != null){
				sql.append("AND ecd.CAR_RETAIL_PRICE = ? ");
				params.add(carDel.getCarRetailPrice());
			}
			if(carDel.getCarTaxPrice() != null){
				sql.append("AND ecd.CAR_TAX_PRICE = ? ");
				params.add(carDel.getCarTaxPrice());
			}
			if(carDel.getCarMileage() != null){
				sql.append("AND ecd.CAR_MILEAGE = ? ");
				params.add(carDel.getCarMileage());
			}
			if(carDel.getCarAuthState() != null){
				sql.append("AND ecd.CAR_AUTH_STATE = ? ");
				params.add(carDel.getCarAuthState());
			}
			if(carDel.getCarDetectState() != null){
				sql.append("AND ecd.CAR_DETECT_STATE = ? ");
				params.add(carDel.getCarDetectState());
			}
			if(carDel.getCarSaleState() != null){
				sql.append("AND ecd.CAR_SALE_STATE = ? ");
				params.add(carDel.getCarSaleState());
			}
			if(carDel.getCarStockState() != null){
				sql.append("AND ecd.CAR_Stock_STATE = ? ");
				params.add(carDel.getCarStockState());
			}
			if(carDel.getCarTransferState() != null){
				sql.append("AND ecd.CAR_TRANSFER_STATE = ? ");
				params.add(carDel.getCarTransferState());
			}
			if(carDel.getCarOrderState() != null){
				sql.append("AND ecd.CAR_ORDER_STATE = ? ");
				params.add(carDel.getCarOrderState());
			}
			if(carDel.getCarOrderState() != null){
				sql.append("AND ecd.CAR_ORDER_STATE = ? ");
				params.add(carDel.getCarOrderState());
			}
			if(carDel.getCarBargainPriceState() != null){
				sql.append("AND ecd.CAR_BARGAIN_PRICE_STATE = ? ");
				params.add(carDel.getCarBargainPriceState());
			}
			if(StringUtils.isNotEmpty(carDel.getCarRfid())) {
				sql.append("AND ecd.CAR_RFID = ? ");
				params.add(carDel.getCarRfid());
			}
		}
	}
}
